using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;
using Dapper;
using admin.core.Repositories;
using admin.core.Entities;
using admin.core.Common;


namespace admin.infrastructure.Repositories
{
    public class DapperRepository<T> : IRepository<T> where T : EntityBase
    {
        private readonly IDbConnection _connection;

        public DapperRepository(IDbConnection connection)
        {
            _connection = connection;
        }

        protected IDbConnection Connection => _connection;

        public async Task<List<T>> GetAllAsync()
        {
            // 需根据实际表名和映射调整
            var tableName = GetTableName();
            var sql = $"SELECT * FROM \"{tableName}\"";
            var result = await _connection.QueryAsync<T>(sql);
            return result.AsList();
        }

        public async Task<T>? GetByIdAsync(Guid id)
        {
            var tableName = GetTableName();
            var sql = $"SELECT * FROM \"{tableName}\" WHERE \"Id\" = @Id";
            var result = await _connection.QuerySingleOrDefaultAsync<T>(sql, new { Id = id });
            return result;
        }

        public async Task<T> AddAsync(T entity)
        {
            var type = typeof(T);
            var tableName = GetTableName();

            // 自动生成ID
            var idProperty = type.GetProperty("Id");
            if (idProperty != null && idProperty.PropertyType == typeof(Guid))
            {
                var currentId = (Guid)idProperty.GetValue(entity);
                if (currentId == Guid.Empty)
                {
                    idProperty.SetValue(entity, Guid.NewGuid());
                }
            }

            // 自动设置时间戳
            var now = DateTime.UtcNow;
            var createdAtProperty = type.GetProperty("CreatedAt");
            if (createdAtProperty != null && createdAtProperty.PropertyType == typeof(DateTime))
                createdAtProperty.SetValue(entity, now);

            var updatedAtProperty = type.GetProperty("UpdatedAt");
            if (updatedAtProperty != null && updatedAtProperty.PropertyType == typeof(DateTime))
                updatedAtProperty.SetValue(entity, now);

            // 只校验字符串类型的 NOT NULL 字段
            var requiredStringProps = type.GetProperties()
                .Where(p =>
                    p.CanRead && p.CanWrite &&
                    p.PropertyType == typeof(string) &&
                    !p.Name.EndsWith("Id") && // 排除外键
                    !p.Name.Equals("Code", StringComparison.OrdinalIgnoreCase) && // 排除可选编号
                    !p.Name.Equals("Remarks", StringComparison.OrdinalIgnoreCase) && // 排除备注
                    !p.Name.Equals("AvatarUrl", StringComparison.OrdinalIgnoreCase) // 排除头像
                )
                .Where(p => string.IsNullOrEmpty((string)p.GetValue(entity)))
                .ToList();

            if (requiredStringProps.Any())
            {
                var missingFields = string.Join(", ", requiredStringProps.Select(p => p.Name));
                throw new ArgumentException($"以下字符串字段不能为空: {missingFields}");
            }

            // 过滤掉集合类型（导航属性）
            var props = type.GetProperties()
                .Where(p => p.CanRead && p.CanWrite && !typeof(System.Collections.IEnumerable).IsAssignableFrom(p.PropertyType) || p.PropertyType == typeof(string))
                .ToList();

            var columns = string.Join(", ", props.Select(p => $"\"{p.Name}\""));
            var values = string.Join(", ", props.Select(p => "@" + p.Name));
            var sql = $"INSERT INTO \"{tableName}\" ({columns}) VALUES ({values});";
            await _connection.ExecuteAsync(sql, entity);
            return entity;
        }

        public async Task UpdateAsync(T entity)
        {
            // 设置更新时间
            entity.UpdatedAt = DateTime.UtcNow;
            
            var type = typeof(T);
            var tableName = GetTableName();
            var props = type.GetProperties()
                .Where(p => p.CanRead && p.CanWrite && p.Name != "Id" &&
                    !(typeof(System.Collections.IEnumerable).IsAssignableFrom(p.PropertyType) && p.PropertyType != typeof(string)))
                .ToList();
            var setClause = string.Join(", ", props.Select(p => $"\"{p.Name}\" = @{p.Name}"));
            var sql = $"UPDATE \"{tableName}\" SET {setClause} WHERE \"Id\" = @Id";
            await _connection.ExecuteAsync(sql, entity);
        }

        public async Task DeleteAsync(T entity)
        {
            // 逻辑删除：设置 IsDeleted = true
            entity.IsDeleted = true;
            await UpdateAsync(entity);
        }

        public async Task<bool> DeleteAsync(Guid id)
        {
            try
            {
                var tableName = GetTableName();
                var sql = $"UPDATE \"{tableName}\" SET \"IsDeleted\" = true, \"UpdatedAt\" = @UpdatedAt WHERE \"Id\" = @Id";
                var affectedRows = await _connection.ExecuteAsync(sql, new { Id = id, UpdatedAt = DateTime.UtcNow });
                return affectedRows > 0;
            }
            catch
            {
                return false;
            }
        }

        // 分页查询
        public async Task<PaginationResult<T>> GetPagedAsync(PaginationRequest request)
        {
            var tableName = GetTableName();
            var sql = $"SELECT * FROM \"{tableName}\" WHERE \"IsDeleted\" = false";
            
            // 添加搜索条件
            if (!string.IsNullOrEmpty(request.SearchTerm))
            {
                sql += " AND \"UserName\" ILIKE @SearchTerm";
            }
            
            // 获取总数
            var countSql = $"SELECT COUNT(*) FROM \"{tableName}\" WHERE \"IsDeleted\" = false";
            if (!string.IsNullOrEmpty(request.SearchTerm))
            {
                countSql += " AND \"UserName\" ILIKE @SearchTerm";
            }
            
            var parameters = new DynamicParameters();
            if (!string.IsNullOrEmpty(request.SearchTerm))
            {
                parameters.Add("@SearchTerm", $"%{request.SearchTerm}%");
            }
            
            var totalCount = await _connection.ExecuteScalarAsync<int>(countSql, parameters);
            
            // 分页查询
            sql += $" ORDER BY \"Id\" LIMIT {request.PageSize} OFFSET {request.Skip}";
            var items = await _connection.QueryAsync<T>(sql, parameters);
            
            return new PaginationResult<T>(items.AsList(), totalCount, request.PageNumber, request.PageSize);
        }

        private string GetTableName()
        {
            var type = typeof(T);
            var typeName = type.Name;
            
            // 表名映射
            return typeName switch
            {
                "AppImage" => "AppImage", // 保持单数形式
                "AppUser" => "AppUser",
                "AppRole" => "AppRole",
                "AppPermission" => "AppPermission",
                "AppResource" => "AppResource",
                _ => typeName
            };
        }
    }
}